package org.adg.code.dao;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import org.adg.code.common.AdgProperties;
import org.adg.code.model.AdgProps;
import org.adg.code.model.DownloadFile;
import org.adg.code.model.NewReportReturn;
import org.adg.code.model.ReportValues;
import org.adg.code.model.SaveReportValues;
import org.adg.code.model.TemplateMultiValues;

public class ReportDaoImpl implements ReportDao {
	
	public ResultSet getReportCatNames() {
		// TODO Auto-generated method stub
		ResultSet resultSet=null;;
		DBConnection dbc=new DBConnection();
		try
		{
			Connection con = dbc.getConnection();
			Statement statement = con.createStatement();
			resultSet = statement.executeQuery("select name from report;");
			return resultSet;
		}
		catch(SQLException se)
		{
			System.out.println("Save Proj Error : " + se);
		}
		return resultSet;
	
	}
	
	
	public NewReportReturn getReportSet(String rname,String uname) {
		// TODO Auto-generated method stub
		DBConnection dbc=new DBConnection();
		Connection con = dbc.getConnection();
		NewReportReturn nr=new NewReportReturn();
		int tfid;
		int i=0;
		
		try
		{
			Statement statement = con.createStatement();
			ResultSet rs = statement.executeQuery("select template_filename_id from report where name=\'"+rname+"\';");
			rs.next();
			tfid=Integer.parseInt(rs.getString(1));
			statement.close();
		
			statement = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = statement.executeQuery("select report_particulars_id from template where template_filename_id="+tfid+";");
			rs.afterLast();
			rs.previous();
			int size=rs.getRow();
			rs.beforeFirst();
			int[] rpid=new int[size];
			i=0;
			while(rs.next())
			{
				rpid[i]=Integer.parseInt(rs.getString(1));
				i++;
			}
			statement.close();
		
			
			int[] rptid=new int[i];
			int[] uitid=new int[i];
			for(int j=0;j<i;j++)
			{
				statement = con.createStatement();
				rs = statement.executeQuery("select report_particulars_type_id,ui_type_id from report_particulars where id="+rpid[j]+";");
				rs.next();
				rptid[j]=Integer.parseInt(rs.getString(1));
				uitid[j]=Integer.parseInt(rs.getString(2));
				statement.close();
			}
			
			TemplateMultiValues[] tmv=new TemplateMultiValues[i+1];
			ArrayList<String> Arptid=new ArrayList<String>();
			ArrayList<String> Arptname=new ArrayList<String>();
			ArrayList<String> Auitype=new ArrayList<String>();
			Statement statement1;
			ResultSet rs1;
			String str="",uitype="";
			
			for(int j=0;j<i;j++)
			{
				statement = con.createStatement();
				statement1 = con.createStatement();
				rs = statement.executeQuery("select name from report_particular_type where id="+rptid[j]+";");
				rs1 = statement1.executeQuery("select description from ui_type where id="+uitid[j]+";");
				rs.next();
				rs1.next();
				str=Integer.toString(rptid[j]);				
				Arptid.add(str);
				Arptname.add(rs.getString(1));
				Auitype.add(rs1.getString(1));
				uitype=rs1.getString(1);
				statement.close();
				statement1.close();
				
				if(uitype.equalsIgnoreCase("Selectbox")||uitype.equalsIgnoreCase("Radiobutton"))
				{
					List<String> l=new ArrayList<String>();
					statement = con.createStatement();
					rs = statement.executeQuery("select values from option_values where report_particulars_id="+rpid[j]+";");
					while(rs.next())
					{
						l.add(rs.getString(1));
						
					}
					statement.close();
					tmv[j]=new TemplateMultiValues();
					tmv[j].setList(l);
					
					
				}
			}
			
			statement = con.createStatement();
			rs = statement.executeQuery("select username from report_user where login_id=(select id from login where username=\'"+uname+"\');");
			
			nr.setrptid(Arptid);
			nr.setRptName(Arptname);
			nr.setuitype(Auitype);
			nr.setUnames(rs);
			nr.setTmv(tmv);
			return nr;
		}
		catch(SQLException se)
		{
			System.out.println("Save Proj Error : " + se);
		}
		return nr;
	}


	
	
	
	public boolean saveReport(SaveReportValues[] srv,int nov) {
		// TODO Auto-generated method stub
		DBConnection dbc=new DBConnection();
		Connection con = dbc.getConnection();
		int rid=0,ruid=0,grid=0;
		
		try
		{
				Statement statement=con.createStatement();
				ResultSet rs=null;
				rs= statement.executeQuery("select id from report where name=\'"+srv[0].getrepCat()+"\';");
				if(rs.next())
				{
					rid=Integer.parseInt(rs.getString(1));
				}
				statement.close();
				
								
				statement=con.createStatement();
				rs= statement.executeQuery("select id from report_user where username=\'"+srv[0].getuname()+"\';");
				if(rs.next())
				{
					ruid=Integer.parseInt(rs.getString(1));
					
				}
				statement.close();
				
								
				Calendar currentDate = Calendar.getInstance();
				SimpleDateFormat formatter=	new SimpleDateFormat("yyyy/MMM/dd HH:mm:ss");
				String dateNow = formatter.format(currentDate.getTime());
				
				
				
				PreparedStatement pstat = null;
				statement=con.createStatement();
				pstat = con.prepareStatement("insert into generated_reports(report_id,name,creation_date,modified_date,reference,report_user_id) values("+rid+",?,\'"+dateNow+"\',\'"+dateNow+"\',?,"+ruid+") returning id;");
				pstat.setString(1, srv[0].getrepName());
				pstat.setString(2, srv[0].getref());
				rs=pstat.executeQuery();
				if(rs.next())
				{
					grid=Integer.parseInt(rs.getString(1));
				}
				statement.close();
				pstat.close();
				
				int f=0;
				for(int i=1;i<=nov;i++)
				{
					
					pstat = con.prepareStatement("insert into generated_reports_data(generated_reports_id,data_value,report_particular_type_id) values ("+grid+",?,"+srv[i].getrptid()+");");
					pstat.setString(1, srv[i].getvalue());
					f=pstat.executeUpdate();
					pstat.close();
				}
				if(f!=0)
					return true;
			}
			catch(SQLException se)
			{
				System.out.println("Save Proj Error : " + se);
			}
		
	
		return false;
	}
	
	
	public ReportValues getReportValues(String rname) {
		// TODO Auto-generated method stub
		DBConnection dbc=new DBConnection();
		Connection con = dbc.getConnection();
		int grid=0,ruid=0;
		ResultSet rs=null;
		ReportValues rv=new ReportValues();
		rv.setReportname(rname);
		try
		{
				Statement statement=con.createStatement();
				rs= statement.executeQuery("select id,reference,report_user_id,report_generation_date from generated_reports where name=\'"+rname+"\';");
				if(rs.next())
				{
					grid=Integer.parseInt(rs.getString(1));
					rv.setRef(rs.getString(2));
					ruid=Integer.parseInt(rs.getString(3));
					if(rs.getString(4)==null)
					{
						rv.setIsgenerated("no");
					}
					else
					{
						rv.setIsgenerated("yes");
					}
				}
				statement.close();
				
				statement=con.createStatement();
				rs= statement.executeQuery("select data_value from generated_reports_data where generated_reports_id="+grid+";");
				rv.setRs(rs);
				
				
				
				Statement statement1=con.createStatement();
				ResultSet rs1= statement1.executeQuery("select username from report_user where id="+ruid+";");
				if(rs1.next())
				{
					rv.setUsername(rs1.getString(1));
				}
				
				
		}
		catch(SQLException se)
		{
			System.out.println("Save Proj Error : " + se);
		}
		return rv;
	}
	
	
	public boolean updateReport(ReportValues[] rv, int numofvalues) {
		// TODO Auto-generated method stub
		DBConnection dbc=new DBConnection();
		Connection con = dbc.getConnection();
		int ruid=0,grid=0;
		try
		{
			Statement statement=con.createStatement();
			ResultSet rs;
			statement=con.createStatement();
			rs= statement.executeQuery("select id from report_user where username=\'"+rv[0].getUsername()+"\';");
			if(rs.next())
			{
				ruid=Integer.parseInt(rs.getString(1));
				
			}
			statement.close();
			
			Calendar currentDate = Calendar.getInstance();
			SimpleDateFormat formatter=	new SimpleDateFormat("yyyy/MMM/dd HH:mm:ss");
			String dateNow = formatter.format(currentDate.getTime());
		
			statement=con.createStatement();
			rs= statement.executeQuery("select id from generated_reports where name=\'"+rv[0].getReportname()+"\';");
			if(rs.next())
			{
				grid=Integer.parseInt(rs.getString(1));
				
			}
			statement.close();
			
			String query="update generated_reports set name=?,modified_date=\'"+dateNow+"\',reference=?,report_user_id="+ruid+" where id="+grid+";";
			PreparedStatement pstat = con.prepareStatement(query);
			pstat.setString(1, rv[0].getReportname());
			pstat.setString(2, rv[0].getRef());
			int i=pstat.executeUpdate();
			pstat.close();
			
			for(int j=1;j<=numofvalues;j++)
			{
				query="update generated_reports_data set data_value=? where generated_reports_id="+grid+" and report_particular_type_id="+rv[j].getRptid()+";";
				pstat = con.prepareStatement(query);
				pstat.setString(1, rv[j].getValues());
				i=pstat.executeUpdate();
				pstat.close();
			}
			if(i!=0)
				return true;
		}
		catch(SQLException se)
		{
			System.out.println("Save Proj Error ud: " + se);
		}
		return false;
	}

	
	public boolean generateReport(ReportValues[] rv, int numofvalues) {
		// TODO Auto-generated method stub
		DBConnection dbc=new DBConnection();
		Connection con = dbc.getConnection();
		
		 AdgProperties ADG=new AdgProperties();
         AdgProps adg=ADG.getProperties();
		
		Calendar currentDate = Calendar.getInstance();
		SimpleDateFormat formatter=	new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
		String dateNow = formatter.format(currentDate.getTime());
		try
		{
			String query="update generated_reports set report_generation_date=\'"+dateNow+"\' where name=?;";
			PreparedStatement pstat = con.prepareStatement(query);
			pstat.setString(1, rv[0].getReportname());
			pstat.executeUpdate();
			pstat.close();
		}
		catch(SQLException se)
		{
			System.out.println("Save Proj Error ge: " + se);
		}
		 try
         {
			 
			Statement statement = con.createStatement();
			ResultSet rs = statement.executeQuery("select description from template_filename where id=(select template_filename_id from report where name=\'"+rv[0].getReportcat()+"\');");
			String tempfilename="";
			if(rs.next())
			{	
				tempfilename=(rs.getString(1));
			}
			statement.close();
			
			
			File file = new File(adg.getTempsrc()+"\\"+tempfilename+".xml");
			BufferedReader reader = new BufferedReader(new FileReader(file));
			String line = "", oldtext = "";
			while((line = reader.readLine()) != null)
			{
				oldtext += line + "\r\n";
			}
			reader.close();
           
        
			//To replace a line in a file
         
			String keyid=adg.getKeyidentifier();
			String key="";
			for(int j=1;j<=numofvalues;j++)
			{
				statement = con.createStatement();
				rs = statement.executeQuery("select keywordintemplate from template where report_particulars_id=(select id from report_particulars where report_particulars_type_id="+rv[j].getRptid()+");");
				if(rs.next())
				{
					key=rs.getString(1);
				}
				oldtext = oldtext.replaceAll(keyid+key+keyid, rv[j].getValues());
			}
        
			File d1=new File(adg.getDocsrc());
			if(!(d1.exists()&&d1.isDirectory()))
			{
				File dir = new File(adg.getDocsrc());
				dir.mkdirs();
				
			}
			String appenddate=dateNow.replaceAll("/","");
			appenddate=appenddate.replaceAll(":","");
			appenddate=appenddate.replaceAll("-","");
			appenddate=appenddate.replaceAll(" ","");
			FileWriter writer = new FileWriter(adg.getDocsrc()+"\\"+rv[0].getReportname()+appenddate+".xml");
			writer.write(oldtext);writer.close();
			
         }
		 catch (IOException ioe)
		 {
			 ioe.printStackTrace();
		 } catch (SQLException e) {
			 // TODO Auto-generated catch block
			 e.printStackTrace();
		 }
		 return true;
	}

	
	
	
	public ArrayList<String> getReportNames(String rname,String uname) {
		// TODO Auto-generated method stub
		ResultSet resultSet=null;;
		DBConnection dbc=new DBConnection();
		ArrayList<String> al=new ArrayList<String>();
		try
		{	
			Connection con = dbc.getConnection();
			Statement statement = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			resultSet = statement.executeQuery("select id from report_user where login_id=(select id from login where username=\'"+uname+"\');");
			resultSet.afterLast();
			resultSet.previous();
			int size=resultSet.getRow();
			resultSet.beforeFirst();
			int[] ruid=new int[size];
			int i=0;
			while(resultSet.next())
			{
				ruid[i]=Integer.parseInt(resultSet.getString(1));
				i++;
			}
			statement.close();
			int fkeyruid=0;
			
				statement = con.createStatement();
				resultSet = statement.executeQuery("select name,report_user_id from generated_reports where report_id=(select id from report where name=\'"+rname+"\');");
				while(resultSet.next())
				{
					fkeyruid=Integer.parseInt(resultSet.getString(2));
					for(int k=0;k<i;k++)
						if(fkeyruid==ruid[k])
							al.add(resultSet.getString(1));
				}
				statement.close();
			
			return al;
		}
		catch(SQLException se)
		{
			System.out.println("Save Proj Error : " + se);
		}
		return al;
	}

	
	public boolean removeReport(String rname) {
		// TODO Auto-generated method stub
		DBConnection dbc =new DBConnection();
		int id=0,i=0;
		try
		{
			Connection con = dbc.getConnection();
			Statement statement = con.createStatement();
			ResultSet rs=statement.executeQuery("select id from generated_reports where name=\'"+rname+"\';");
			if(rs.next())
			{
				id=Integer.parseInt(rs.getString(1));
			}
			statement.close();
	//		System.out.println("rname = "+rname+" id = "+id);
			
			String querry="delete from generated_reports_data where generated_reports_id="+id+";";
			PreparedStatement pstat = con.prepareStatement(querry);
			i = pstat.executeUpdate();
			pstat.close();
			
			querry="delete from generated_reports where id="+id+";";
			pstat = con.prepareStatement(querry);
			i = pstat.executeUpdate();
			pstat.close();
			if(i!=0)
				return true;
		}
		catch(SQLException se)
		{
			System.out.println("Save Proj Error : " + se);
		}
		return false;
	}

	
	public  DownloadFile getDownloadFile(String rname) {
		// TODO Auto-generated method stub
		DBConnection dbc =new DBConnection();
		DownloadFile df=new DownloadFile();
		 AdgProperties ADG=new AdgProperties();
         AdgProps adg=ADG.getProperties();
         String appenddate="";
         try {
        	 Connection con = dbc.getConnection();
 			Statement statement = con.createStatement();
 			ResultSet rs=statement.executeQuery("select report_generation_date from generated_reports where name=\'"+rname+"\';");
 			if(rs.next())
 			{
 				appenddate=rs.getString(1);
 			}
 			statement.close();
        	 
        	
        	 appenddate=appenddate.replaceAll("/","");
 			appenddate=appenddate.replaceAll(":","");
 			appenddate=appenddate.replaceAll("-","");
 			appenddate=appenddate.replaceAll(" ","");
        	 
       	String filename=rname+appenddate+".xml";
 			
        	 df.setFilename(filename);
        
        	 File fileToDownload = new File(adg.getDocsrc()+"\\"+filename);
     	 
     		FileInputStream fileInputStream = new FileInputStream(fileToDownload);
     		df.setF(fileInputStream);
     	} catch (FileNotFoundException e) {
     		// TODO Auto-generated catch block
     		e.printStackTrace();
     	} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return df;
	}

	
	public boolean repNameExists(String rname) {
		// TODO Auto-generated method stub
		DBConnection dbc =new DBConnection();
		ResultSet rs=null;
		try
		{
			Connection con = dbc.getConnection();
			Statement statement = con.createStatement();
			rs = statement.executeQuery("select * from generated_reports where name=\'"+rname+"\';");
			if(rs.next())
				return true;
		}
		catch(SQLException se)
		{
			System.out.println("Save Proj Error : " + se);
		}
		
		return false;
	
	}
}
